package zy.dao.report.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.report.ReportDAO;
import zy.dto.report.kpi.KpiAnalysisDayDto;
import zy.dto.report.kpi.KpiAnalysisDto;
import zy.dto.report.kpi.KpiAnalysisMonthDto;
import zy.entity.base.shop.T_Base_Shop;
import zy.entity.batch.sell.T_Batch_SellList;
import zy.entity.buy.enter.T_Buy_EnterList;
import zy.entity.sell.allocate.T_Sell_AllocateList;
import zy.entity.sell.cash.T_Sell_ShopList;
import zy.entity.shop.want.T_Shop_WantList;
import zy.entity.sort.allot.T_Sort_AllotList;
import zy.entity.stock.adjust.T_Stock_AdjustList;
import zy.entity.stock.allocate.T_Stock_AllocateList;
import zy.util.CommonUtil;
import zy.util.StringUtil;
@Repository
public class ReportDAOImpl extends BaseDaoImpl implements ReportDAO {
	
	@Override
	public List<T_Base_Shop> listShop(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		String shopCode = StringUtil.trimString(params.get("shopCode"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT sp_code,sp_name");
		sql.append(" FROM t_base_shop t");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else {
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" WHERE 1 = 1");
		if(StringUtil.isNotEmpty(shopCode)){
			params.put("shopCodeArr", Arrays.asList(shopCode.toString().split(",")));
			sql.append(" AND sp_code IN (:shopCodeArr)");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Shop.class));
	}

	@Override
	public Map<String,Object> sell_max_count(Map<String, Object> paramMap) {
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		Object begindate = paramMap.get("begindate");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		Object amount = paramMap.get("amount");
		Object type = paramMap.get("type");
		Object pd_year = paramMap.get("pd_year");
		Object pd_season = paramMap.get("pd_season");
		Object pd_style = paramMap.get("pd_style");
		Object chkss_day = paramMap.get("chkss_day");
		Object pd_no = paramMap.get("pd_no");
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1) shl_count,SUM(shl_amount) shl_amount");
		sql.append(" FROM (");
		sql.append(" SELECT SUM(shl_amount) shl_amount");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.shl_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			sql.append(" AND sp_code=:sh_shop_code");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		if(StringUtil.isNotEmpty(pd_year)){
			sql.append(" AND p.pd_year=:pd_year");
		}
		if(StringUtil.isNotEmpty(pd_season)){
			sql.append(" AND p.pd_season=:pd_season");
		}
		if(StringUtil.isNotEmpty(pd_style)){
			sql.append(" AND p.pd_style=:pd_style");
		}
		if(StringUtil.isNotEmpty(chkss_day) && StringUtil.trimString(chkss_day).equals("0")){
			if(StringUtil.trimString(type).equals("1")){
				sql.append(" AND p.pd_date <= :pd_date");
			}else {
				sql.append(" AND p.pd_date >= :pd_date");
			}
		}
		if(StringUtil.isNotEmpty(pd_no)){
			sql.append(" AND instr(p.pd_no,:pd_no)>0");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY shl_pd_code");
		if(StringUtil.isNotEmpty(amount)){
			if(StringUtil.trimString(type).equals("1")){
				sql.append(" HAVING SUM(shl_amount)<=:amount");
			}else{
				sql.append(" HAVING SUM(shl_amount)>=:amount");
			}
		}else{
			sql.append(" HAVING SUM(shl_amount)>0");
		}
		sql.append(") t");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), paramMap);
	}

	@Override
	public List<T_Sell_ShopList> sell_max_list(Map<String, Object> paramMap) {
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		Object begindate = paramMap.get("begindate");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		Object amount = paramMap.get("amount");
		Object type = paramMap.get("type");
		Object pd_year = paramMap.get("pd_year");
		Object pd_season = paramMap.get("pd_season");
		Object pd_style = paramMap.get("pd_style");
		Object chkss_day = paramMap.get("chkss_day");
		Object pd_no = paramMap.get("pd_no");
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT shl_pd_code,SUM(shl_amount) shl_amount,pd_sell_price shl_sell_price,");
		sql.append(" pd_name,pd_no,pd_year,pd_unit,pd_season,pd_style,pd_date shl_sysdate,MAX(shl_date) shl_date,");
		sql.append(" TO_DAYS(NOW()) - TO_DAYS(p.pd_date) AS pd_days,");
		sql.append("(select bd_name from t_base_brand bd where bd.bd_code=p.pd_bd_code and bd.companyid=p.companyid LIMIT 1) as bd_name,");//品牌
		sql.append("(select tp_name from t_base_type tp where tp.tp_code=p.pd_tp_code and tp.companyid=p.companyid LIMIT 1) as tp_name");//类别
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.shl_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			sql.append(" AND sp_code=:shl_shop_code");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		if(StringUtil.isNotEmpty(pd_year)){
			sql.append(" AND p.pd_year=:pd_year");
		}
		if(StringUtil.isNotEmpty(pd_season)){
			sql.append(" AND p.pd_season=:pd_season");
		}
		if(StringUtil.isNotEmpty(pd_style)){
			sql.append(" AND p.pd_style=:pd_style");
		}
		if(StringUtil.isNotEmpty(chkss_day) && StringUtil.trimString(chkss_day).equals("0")){
			if(StringUtil.trimString(type).equals("1")){
				sql.append(" AND p.pd_date <= :pd_date");
			}else {
				sql.append(" AND p.pd_date >= :pd_date");
			}
		}
		if(StringUtil.isNotEmpty(pd_no)){
			sql.append(" AND instr(p.pd_no,:pd_no)>0");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY shl_pd_code");
		if(StringUtil.isNotEmpty(amount)){
			if(StringUtil.trimString(type).equals("1")){
				sql.append(" HAVING SUM(shl_amount)<=:amount");
			}else{
				sql.append(" HAVING SUM(shl_amount)>=:amount");
			}
		}else{
			sql.append(" HAVING SUM(shl_amount)>0");
		}
		sql.append(" ORDER BY SUM(shl_amount) DESC");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
	}

	@Override
	public Map<String, Object> selllist_max_count(Map<String, Object> paramMap) {
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		Object begindate = paramMap.get("begindate");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		Object amount = paramMap.get("amount");
		Object type = paramMap.get("type");
		Object pd_year = paramMap.get("pd_year");
		Object pd_season = paramMap.get("pd_season");
		Object pd_style = paramMap.get("pd_style");
		Object chkss_day = paramMap.get("chkss_day");
		Object pd_no = paramMap.get("pd_no");
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1) shl_count,SUM(shl_amount) shl_amount");
		sql.append(" FROM (");
		sql.append(" SELECT SUM(shl_amount) shl_amount");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.shl_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			sql.append(" AND sp_code=:sh_shop_code");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		if(StringUtil.isNotEmpty(pd_year)){
			sql.append(" AND p.pd_year=:pd_year");
		}
		if(StringUtil.isNotEmpty(pd_season)){
			sql.append(" AND p.pd_season=:pd_season");
		}
		if(StringUtil.isNotEmpty(pd_style)){
			sql.append(" AND p.pd_style=:pd_style");
		}
		if(StringUtil.isNotEmpty(chkss_day) && StringUtil.trimString(chkss_day).equals("0")){
			if(StringUtil.trimString(type).equals("1")){
				sql.append(" AND p.pd_date <= :pd_date");
			}else {
				sql.append(" AND p.pd_date >= :pd_date");
			}
		}
		if(StringUtil.isNotEmpty(pd_no)){
			sql.append(" AND instr(p.pd_no,:pd_no)>0");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY shl_sub_code");
		if(StringUtil.isNotEmpty(amount)){
			if(StringUtil.trimString(type).equals("1")){
				sql.append(" HAVING SUM(shl_amount)<=:amount");
			}else{
				sql.append(" HAVING SUM(shl_amount)>=:amount");
			}
		}else{
			sql.append(" HAVING SUM(shl_amount)>0");
		}
		sql.append(") t");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), paramMap);
	}

	@Override
	public List<T_Sell_ShopList> selllist_max_list(Map<String, Object> paramMap) {
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		Object begindate = paramMap.get("begindate");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		Object amount = paramMap.get("amount");
		Object type = paramMap.get("type");
		Object pd_year = paramMap.get("pd_year");
		Object pd_season = paramMap.get("pd_season");
		Object pd_style = paramMap.get("pd_style");
		Object chkss_day = paramMap.get("chkss_day");
		Object pd_no = paramMap.get("pd_no");
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT shl_pd_code,SUM(shl_amount) shl_amount,pd_sell_price shl_sell_price,");
		sql.append(" pd_name,pd_no,pd_year,pd_unit,pd_season,pd_style,pd_date shl_sysdate,MAX(shl_date) shl_date,");
		sql.append(" TO_DAYS(NOW()) - TO_DAYS(p.pd_date) AS pd_days,shl_sub_code,");
		sql.append("(select cr_name from t_base_color cr where cr.cr_code=t.shl_cr_code and cr.companyid=t.companyid LIMIT 1) as cr_name,");//颜色
		sql.append("(select sz_name from t_base_size sz where sz.sz_code=t.shl_sz_code and sz.companyid=t.companyid LIMIT 1) as sz_name,");//尺码
		sql.append("(select br_name from t_base_bra br where br.br_code=t.shl_br_code and br.companyid=t.companyid LIMIT 1) as br_name,");//杯型
		sql.append("(select bd_name from t_base_brand bd where bd.bd_code=p.pd_bd_code and bd.companyid=p.companyid LIMIT 1) as bd_name,");//品牌
		sql.append("(select tp_name from t_base_type tp where tp.tp_code=p.pd_tp_code and tp.companyid=p.companyid LIMIT 1) as tp_name");//类别
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.shl_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			sql.append(" AND sp_code=:shl_shop_code");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		if(StringUtil.isNotEmpty(pd_year)){
			sql.append(" AND p.pd_year=:pd_year");
		}
		if(StringUtil.isNotEmpty(pd_season)){
			sql.append(" AND p.pd_season=:pd_season");
		}
		if(StringUtil.isNotEmpty(pd_style)){
			sql.append(" AND p.pd_style=:pd_style");
		}
		if(StringUtil.isNotEmpty(chkss_day) && StringUtil.trimString(chkss_day).equals("0")){
			if(StringUtil.trimString(type).equals("1")){
				sql.append(" AND p.pd_date <= :pd_date");
			}else {
				sql.append(" AND p.pd_date >= :pd_date");
			}
		}
		if(StringUtil.isNotEmpty(pd_no)){
			sql.append(" AND instr(p.pd_no,:pd_no)>0");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY shl_sub_code");
		if(StringUtil.isNotEmpty(amount)){
			if(StringUtil.trimString(type).equals("1")){
				sql.append(" HAVING SUM(shl_amount)<=:amount");
			}else{
				sql.append(" HAVING SUM(shl_amount)>=:amount");
			}
		}else{
			sql.append(" HAVING SUM(shl_amount)>0");
		}
		sql.append(" ORDER BY SUM(shl_amount) DESC");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
	}

	@Override
	public List<T_Sell_ShopList> sell_compare(Map<String, Object> paramMap) {
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		Object begindate = paramMap.get("begindate");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		Object type = paramMap.get("type");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT shl_id,MONTH(shl_date) shl_date,");
		String _type = "";
		if(type.equals("brand")){
			_type = " pd_bd_code";
			sql.append(" pd_bd_code bd_code,");
			sql.append(" (SELECT bd_name FROM t_base_brand  b WHERE b.bd_code=p.pd_bd_code AND b.companyid=p.companyid LIMIT 1) bd_name,");
		}
		if(type.equals("type")){
			_type = " pd_tp_code";
			sql.append(" pd_tp_code bd_code,");
			sql.append(" (SELECT tp_name FROM t_base_type  b WHERE b.tp_code=p.pd_tp_code AND b.companyid=p.companyid LIMIT 1) bd_name,");
		}
		if(type.equals("emp")){
			_type = " shl_main";
			sql.append(" shl_main bd_code,");
			sql.append(" (SELECT em_name FROM t_base_emp  b WHERE b.em_code=t.shl_main AND b.companyid=t.companyid LIMIT 1) bd_name,");
		}
		if(type.equals("shop")){
			_type = " shl_shop_code";
			sql.append(" shl_shop_code bd_code,");
			sql.append(" sp_name bd_name,");
		}
		sql.append(" SUM(shl_amount) shl_amount,SUM(shl_money) shl_money,SUM(shl_money-(shl_cost_price*shl_amount)) shl_profit");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.shl_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			sql.append(" AND sp_code=:shl_shop_code");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY "+_type+",MONTH(shl_date)");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
	}

	public Integer sellStock_count(Map<String,Object> paramMap){
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		Object pd_year = paramMap.get("pd_year");
		Object pd_season = paramMap.get("pd_season");
		Object begindate = paramMap.get("begindate");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1)");
		sql.append(" FROM (SELECT 1");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.shl_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			paramMap.put("shop_codes", StringUtil.parseList(StringUtil.trimString(shl_shop_code)));
			sql.append(" AND sp_code IN (:shop_codes)");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" WHERE shl_state=0");//只是零售的才计算存销比
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(paramMap.get("pd_code"))){
			sql.append(" AND pd_code=:pd_code");
		}
		if(StringUtil.isNotEmpty(pd_year)){
			sql.append(" AND pd_year=:pd_year");
		}
		if(StringUtil.isNotEmpty(pd_season)){
			sql.append(" AND pd_season=:pd_season");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY shl_sub_code");
		sql.append(" ) t");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), paramMap ,Integer.class);
	}
	public List<T_Sell_ShopList> sellStock_list(Map<String,Object> paramMap){
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		Object pd_year = paramMap.get("pd_year");
		Object pd_season = paramMap.get("pd_season");
		Object begindate = paramMap.get("begindate");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT shl_id,sum(shl_amount) shl_amount,shl_sub_code,shl_shop_code,pd_no,pd_name,pd_sell_price shl_sell_price,");
		sql.append(" (SELECT cr_name FROM t_base_color c WHERE c.cr_code=t.shl_cr_code AND c.companyid=t.companyid) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size c WHERE c.sz_code=t.shl_sz_code AND c.companyid=t.companyid) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra c WHERE c.br_code=t.shl_br_code AND c.companyid=t.companyid) AS br_name");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.shl_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			paramMap.put("shop_codes", StringUtil.parseList(StringUtil.trimString(shl_shop_code)));
			sql.append(" AND sp_code IN (:shop_codes)");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" WHERE shl_state=0");//只是零售的才计算存销比
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(paramMap.get("pd_code"))){
			sql.append(" AND pd_code=:pd_code");
		}
		if(StringUtil.isNotEmpty(pd_year)){
			sql.append(" AND pd_year=:pd_year");
		}
		if(StringUtil.isNotEmpty(pd_season)){
			sql.append(" AND pd_season=:pd_season");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY shl_sub_code,shl_shop_code");
		sql.append(" ORDER BY shl_sub_code");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
	}

	@Override
	public Map<String, KpiAnalysisDto> kpi_analysis(Map<String, Object> params){
		final Map<String, KpiAnalysisDto> resultMap = new HashMap<String, KpiAnalysisDto>();
		StringBuffer sql = new StringBuffer();
		//进店量、接单人数、试穿人数
		sql.append(" SELECT da_shop_code,SUM(da_come) AS comeAmount,SUM(da_receive) AS receiveAmount,SUM(da_try) AS tryAmount");
		sql.append(" FROM t_sell_day");
		sql.append(" WHERE 1=1");
		sql.append(" AND da_date >= :begindate");
		sql.append(" AND da_date <= :enddate");
		sql.append(" AND da_shop_code IN(:shopCodes)");
		sql.append(" AND companyid = :companyid");
		sql.append(" GROUP BY da_shop_code");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String shopcode = rs.getString("da_shop_code");
				if(!resultMap.containsKey(shopcode)){
					resultMap.put(shopcode, new KpiAnalysisDto());
				}
				resultMap.get(shopcode).setComeamount(rs.getInt("comeAmount"));
				resultMap.get(shopcode).setReceiveamount(rs.getInt("receiveAmount"));
				resultMap.get(shopcode).setTryamount(rs.getInt("tryAmount"));
				return null;
			};
		});
		//销售数量、销售金额、零售金额、成本金额
		sql.setLength(0);
		sql.append(" SELECT shl_shop_code");
		sql.append(" ,IFNULL(SUM(shl_amount),0) AS sellAmount");
		sql.append(" ,IFNULL(SUM(shl_money),0) AS sellMoney");
		sql.append(" ,IFNULL(SUM(shl_sell_price*shl_amount),0) AS retailMoney");
		sql.append(" ,IFNULL(SUM(shl_cost_price*shl_amount),0) AS costMoney");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code IN(:shopCodes)");
		sql.append(" AND shl_date >= :begindate");
		sql.append(" AND shl_date <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY shl_shop_code");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String shopcode = rs.getString("shl_shop_code");
				if(!resultMap.containsKey(shopcode)){
					resultMap.put(shopcode, new KpiAnalysisDto());
				}
				resultMap.get(shopcode).setSellamount(rs.getInt("sellAmount"));
				resultMap.get(shopcode).setSellmoney(rs.getDouble("sellMoney"));
				resultMap.get(shopcode).setRetailmoney(rs.getDouble("retailMoney"));
				resultMap.get(shopcode).setCostmoney(rs.getDouble("costMoney"));
				resultMap.get(shopcode).setProfits(rs.getDouble("sellMoney")-rs.getDouble("costMoney"));
				return null;
			};
		});
		//会员消费
		sql.setLength(0);
		sql.append(" SELECT shl_shop_code,IFNULL(SUM(shl_money),0) AS sellMoney_vip");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code IN(:shopCodes)");
		sql.append(" AND shl_date >= :begindate");
		sql.append(" AND shl_date <= :enddate");
		sql.append(" AND shl_vip_code != ''");
		sql.append(" AND shl_vip_code IS NOT NULL");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY shl_shop_code");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String shopcode = rs.getString("shl_shop_code");
				if(!resultMap.containsKey(shopcode)){
					resultMap.put(shopcode, new KpiAnalysisDto());
				}
				resultMap.get(shopcode).setSellmoney_vip(rs.getDouble("sellMoney_vip"));
				return null;
			};
		});
		//成交单数
		sql.setLength(0);
		sql.append(" SELECT shl_shop_code,shl_state,COUNT(DISTINCT shl_number) AS dealCount");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code IN(:shopCodes)");
		sql.append(" AND shl_date >= :begindate");
		sql.append(" AND shl_date <= :enddate");
		sql.append(" AND shl_state IN(0,1)");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY shl_shop_code,shl_state");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String shopcode = rs.getString("shl_shop_code");
				if(!resultMap.containsKey(shopcode)){
					resultMap.put(shopcode, new KpiAnalysisDto());
				}
				if("0".equals(rs.getString("shl_state"))){
					resultMap.get(shopcode).setDealcount(resultMap.get(shopcode).getDealcount() + rs.getInt("dealCount"));
				}else {
					resultMap.get(shopcode).setDealcount(resultMap.get(shopcode).getDealcount() - rs.getInt("dealCount"));
				}
				return null;
			};
		});
		//开卡人数
		sql.setLength(0);
		sql.append(" SELECT vm_shop_code,COUNT(1) AS vipCount");
		sql.append(" FROM t_vip_member t");
		sql.append(" WHERE 1=1");
		sql.append(" AND vm_shop_code IN(:shopCodes)");
		sql.append(" AND vm_sysdate >= :begindate");
		sql.append(" AND vm_sysdate <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY vm_shop_code");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String shopcode = rs.getString("vm_shop_code");
				if(!resultMap.containsKey(shopcode)){
					resultMap.put(shopcode, new KpiAnalysisDto());
				}
				resultMap.get(shopcode).setVipcount(rs.getInt("vipCount"));
				return null;
			};
		});
		//新增储值卡
		sql.setLength(0);
		sql.append(" SELECT cd_shop_code,COUNT(1) AS cardCount");
		sql.append(" FROM t_sell_card t");
		sql.append(" WHERE 1=1");
		sql.append(" AND cd_shop_code IN(:shopCodes)");
		sql.append(" AND cd_grantdate >= :begindate");
		sql.append(" AND cd_grantdate <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY cd_shop_code");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String shopcode = rs.getString("cd_shop_code");
				if(!resultMap.containsKey(shopcode)){
					resultMap.put(shopcode, new KpiAnalysisDto());
				}
				resultMap.get(shopcode).setCardcount(rs.getInt("cardCount"));
				return null;
			};
		});
		//连带率=销售数量/成交单数,会员消费占比,平均件单价,平均客单价
		for (String key : resultMap.keySet()) {
			KpiAnalysisDto dto = resultMap.get(key);
			if(dto.getDealcount() != 0){
				dto.setJoint_rate(dto.getSellamount() / (double) dto.getDealcount());
				dto.setAvg_sellprice(dto.getSellmoney() / (double) dto.getDealcount());
			}else {
				dto.setJoint_rate(0d);
				dto.setAvg_sellprice(0d);
			}
			if(dto.getSellmoney() != 0){
				dto.setSellmoney_vip_proportion(100 * dto.getSellmoney_vip() / dto.getSellmoney());
			}else {
				dto.setSellmoney_vip_proportion(0d);
			}
			if(dto.getSellamount() != 0){
				dto.setAvg_price(dto.getSellmoney()/dto.getSellamount());
			}else {
				dto.setAvg_price(0d);
			}
			if(dto.getRetailmoney() != 0){
				dto.setAvg_discount(dto.getSellmoney()/dto.getRetailmoney());
			}else {
				dto.setAvg_discount(0d);
			}
		}
		return resultMap;
	}

	@Override
	public Map<String, KpiAnalysisMonthDto> kpi_analysis_month(Map<String, Object> params) {
		final Map<String, KpiAnalysisMonthDto> resultMap = new HashMap<String, KpiAnalysisMonthDto>();
		StringBuffer sql = new StringBuffer();
		//进店量
		sql.append(" SELECT MONTH(da_date) AS month,SUM(da_come) AS comeAmount,SUM(da_receive) AS receiveAmount");
		sql.append(" FROM t_sell_day");
		sql.append(" WHERE 1=1");
		sql.append(" AND da_date >= :begindate");
		sql.append(" AND da_date <= :enddate");
		sql.append(" AND da_shop_code IN(:shopCodes)");
		sql.append(" AND companyid = :companyid");
		sql.append(" GROUP BY MONTH(da_date)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String month = rs.getString("month");
				if(!resultMap.containsKey(month)){
					resultMap.put(month, new KpiAnalysisMonthDto());
				}
				resultMap.get(month).setComeamount(rs.getInt("comeAmount"));
				resultMap.get(month).setReceiveamount(rs.getInt("receiveAmount"));
				return null;
			};
		});
		//计划金额
		sql.setLength(0);
		sql.append(" SELECT pm_month AS month,IFNULL(SUM(pm_sell_money_plan),0) AS planMoney");
		sql.append(" FROM t_shop_plan t");
		sql.append(" JOIN t_shop_plan_month pm ON pm_number = pl_number AND pm.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND pl_shop_code IN(:shopCodes)");
		sql.append(" AND pl_ar_state = 1");
		sql.append(" AND pl_year = :year");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY pm_month");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String month = rs.getString("month");
				if(!resultMap.containsKey(month)){
					resultMap.put(month, new KpiAnalysisMonthDto());
				}
				resultMap.get(month).setPlanmoney(rs.getDouble("planMoney"));
				return null;
			};
		});
		//销售数量、销售金额、零售金额、成本金额
		sql.setLength(0);
		sql.append(" SELECT MONTH(shl_date) AS month");
		sql.append(" ,IFNULL(SUM(shl_amount),0) AS sellAmount");
		sql.append(" ,IFNULL(SUM(shl_money),0) AS sellMoney");
		sql.append(" ,IFNULL(SUM(shl_sell_price*shl_amount),0) AS retailMoney");
		sql.append(" ,IFNULL(SUM(shl_cost_price*shl_amount),0) AS costMoney");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code IN(:shopCodes)");
		sql.append(" AND shl_date >= :begindate");
		sql.append(" AND shl_date <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY MONTH(shl_date)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String month = rs.getString("month");
				if(!resultMap.containsKey(month)){
					resultMap.put(month, new KpiAnalysisMonthDto());
				}
				resultMap.get(month).setSellamount(rs.getInt("sellAmount"));
				resultMap.get(month).setSellmoney(rs.getDouble("sellMoney"));
				resultMap.get(month).setRetailmoney(rs.getDouble("retailMoney"));
				resultMap.get(month).setCostmoney(rs.getDouble("costMoney"));
				resultMap.get(month).setProfits(rs.getDouble("sellMoney")-rs.getDouble("costMoney"));
				return null;
			};
		});
		//会员消费
		sql.setLength(0);
		sql.append(" SELECT MONTH(shl_date) AS month,IFNULL(SUM(shl_money),0) AS sellMoney_vip");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code IN(:shopCodes)");
		sql.append(" AND shl_date >= :begindate");
		sql.append(" AND shl_date <= :enddate");
		sql.append(" AND shl_vip_code != ''");
		sql.append(" AND shl_vip_code IS NOT NULL");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY MONTH(shl_date)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String month = rs.getString("month");
				if(!resultMap.containsKey(month)){
					resultMap.put(month, new KpiAnalysisMonthDto());
				}
				resultMap.get(month).setSellmoney_vip(rs.getDouble("sellMoney_vip"));
				return null;
			};
		});
		//抹零金额
		sql.setLength(0);
		sql.append(" SELECT MONTH(sh_date) AS month, SUM(sh_lost_money) AS lost_money");
		sql.append(" FROM t_sell_shop t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sh_shop_code IN(:shopCodes)");
		sql.append(" AND sh_date >= :begindate");
		sql.append(" AND sh_date <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY MONTH(sh_date)");
		System.out.println(sql);
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String month = rs.getString("month");
				if(!resultMap.containsKey(month)){
					resultMap.put(month, new KpiAnalysisMonthDto());
				}
				resultMap.get(month).setLost_money(rs.getDouble("lost_money"));
				return null;
			};
		});
		//成交单数
		sql.setLength(0);
		sql.append(" SELECT MONTH(shl_date) AS month,shl_state,COUNT(DISTINCT shl_number) AS dealCount");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code IN(:shopCodes)");
		sql.append(" AND shl_date >= :begindate");
		sql.append(" AND shl_date <= :enddate");
		sql.append(" AND shl_state IN(0,1)");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY MONTH(shl_date),shl_state");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String month = rs.getString("month");
				if(!resultMap.containsKey(month)){
					resultMap.put(month, new KpiAnalysisMonthDto());
				}
				if("0".equals(rs.getString("shl_state"))){
					resultMap.get(month).setDealcount(resultMap.get(month).getDealcount() + rs.getInt("dealCount"));
				}else {
					resultMap.get(month).setDealcount(resultMap.get(month).getDealcount() - rs.getInt("dealCount"));
				}
				return null;
			};
		});
		//开卡人数
		sql.setLength(0);
		sql.append(" SELECT MONTH(vm_sysdate) AS month,COUNT(1) AS vipCount");
		sql.append(" FROM t_vip_member t");
		sql.append(" WHERE 1=1");
		sql.append(" AND vm_shop_code IN(:shopCodes)");
		sql.append(" AND vm_sysdate >= :begindate");
		sql.append(" AND vm_sysdate <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY MONTH(vm_sysdate)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String month = rs.getString("month");
				if(!resultMap.containsKey(month)){
					resultMap.put(month, new KpiAnalysisMonthDto());
				}
				resultMap.get(month).setVipcount(rs.getInt("vipCount"));
				return null;
			};
		});
		//新增储值卡
		sql.setLength(0);
		sql.append(" SELECT MONTH(cd_grantdate) AS month,COUNT(1) AS cardCount");
		sql.append(" FROM t_sell_card t");
		sql.append(" WHERE 1=1");
		sql.append(" AND cd_shop_code IN(:shopCodes)");
		sql.append(" AND cd_grantdate >= :begindate");
		sql.append(" AND cd_grantdate <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY MONTH(cd_grantdate)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String month = rs.getString("month");
				if(!resultMap.containsKey(month)){
					resultMap.put(month, new KpiAnalysisMonthDto());
				}
				resultMap.get(month).setCardcount(rs.getInt("cardCount"));
				return null;
			};
		});
		//优惠券
		sql.setLength(0);
		sql.append(" SELECT MONTH(ecu_date) month,COUNT(1) AS  ecouponCount");
		sql.append(" FROM t_sell_ecoupon_user t");
		sql.append(" WHERE 1=1");
		sql.append(" AND ecu_shop_code IN(:shopCodes)");
		sql.append(" AND ecu_date >= :begindate");
		sql.append(" AND ecu_date <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY MONTH(ecu_date)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String month = rs.getString("month");
				if(!resultMap.containsKey(month)){
					resultMap.put(month, new KpiAnalysisMonthDto());
				}
				resultMap.get(month).setEcouponcount(rs.getInt("ecouponCount"));
				return null;
			};
		});
		
		//费用支出金额
		sql.setLength(0);
		sql.append(" SELECT MONTH(epl_sharedate) AS month,SUM(epl_money) AS money");
		sql.append(" FROM t_money_expense t");
		sql.append(" JOIN t_money_expenselist epl ON epl_number = ep_number AND epl.companyid = t.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = ep_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND ep_shop_code IN(:shopCodes)");
		sql.append(" AND epl_sharedate >= :begindate");
		sql.append(" AND epl_sharedate <= :enddate");
		sql.append(" AND ep_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY MONTH (epl_sharedate)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String month = rs.getString("month");
				if(!resultMap.containsKey(month)){
					resultMap.put(month, new KpiAnalysisMonthDto());
				}
				resultMap.get(month).setExpense_money(rs.getDouble("money"));
				return null;
			};
		});
		
		
		//成交率=成交单数/接待人数,连带率=销售数量/成交单数,会员消费占比,平均件单价,平均客单价,净利润=利润-费用支出
		for (String key : resultMap.keySet()) {
			KpiAnalysisMonthDto dto = resultMap.get(key);
			if(dto.getDealcount() != 0){
				dto.setJoint_rate(dto.getSellamount() / (double) dto.getDealcount());
				dto.setAvg_sellprice(dto.getSellmoney() / (double) dto.getDealcount());
			}else {
				dto.setJoint_rate(0d);
				dto.setAvg_sellprice(0d);
			}
			if(dto.getReceiveamount() != 0){
				dto.setDeal_rate(100 * dto.getDealcount() / (double) dto.getReceiveamount());
			}else {
				dto.setDeal_rate(0d);
			}
			if(dto.getSellmoney() != 0){
				dto.setSellmoney_vip_proportion(100 * dto.getSellmoney_vip() / dto.getSellmoney());
				dto.setProfits_rate(100 * dto.getProfits() / dto.getSellmoney());
			}else {
				dto.setSellmoney_vip_proportion(0d);
				dto.setProfits_rate(0d);
			}
			if(dto.getSellamount() != 0){
				dto.setAvg_price(dto.getSellmoney()/dto.getSellamount());
			}else {
				dto.setAvg_price(0d);
			}
			if(dto.getPlanmoney() != 0){
				dto.setComplete_rate(100 * dto.getSellmoney() / dto.getPlanmoney());
			}else {
				dto.setComplete_rate(0d);
			}
			dto.setNet_profits(dto.getProfits() - dto.getExpense_money());
		}
		return resultMap;
	}

	@Override
	public Map<String, KpiAnalysisDayDto> kpi_analysis_day(Map<String, Object> params) {
		final Map<String, KpiAnalysisDayDto> resultMap = new HashMap<String, KpiAnalysisDayDto>();
		StringBuffer sql = new StringBuffer();
		//天气
		sql.append(" SELECT DAY(we_date) day,we_max_tmp,we_min_tmp,");
		sql.append(" (SELECT we_name FROM common_weather WHERE we_id = t.we_we_id LIMIT 1) AS we_name");
		sql.append(" FROM t_sell_weather t");
		sql.append(" WHERE 1=1");
		sql.append(" AND we_date >= :begindate");
		sql.append(" AND we_date <= :enddate");
		sql.append(" AND we_shop_code IN(:shopCodes)");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY DAY(we_date)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String day = rs.getString("day");
				if(!resultMap.containsKey(day)){
					resultMap.put(day, new KpiAnalysisDayDto());
				}
				resultMap.get(day).setWe_name(rs.getString("we_name"));
				resultMap.get(day).setWe_max_tmp(rs.getString("we_max_tmp"));
				resultMap.get(day).setWe_min_tmp(rs.getString("we_min_tmp"));
				return null;
			};
		});
		//计划金额
		sql.setLength(0);
		sql.append(" SELECT mpd_day AS day,SUM(mpd_sell_money_plan) AS planMoney");
		sql.append(" FROM t_shop_monthplan t");
		sql.append(" JOIN t_shop_monthplan_day mpd ON mp_number = mpd_number AND t.companyid = mpd.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND mp_ar_state = 1");
		sql.append(" AND mp_shop_code IN(:shopCodes)");
		sql.append(" AND mp_year = :year");
		sql.append(" AND mp_month = :month");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY mpd_day");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String day = rs.getString("day");
				if(!resultMap.containsKey(day)){
					resultMap.put(day, new KpiAnalysisDayDto());
				}
				resultMap.get(day).setPlanmoney(rs.getDouble("planMoney"));
				return null;
			};
		});
		//进店量、接待人数
		sql.setLength(0);
		sql.append(" SELECT DAY(da_date) AS day,SUM(da_come) AS comeAmount,SUM(da_receive) AS receiveAmount");
		sql.append(" FROM t_sell_day");
		sql.append(" WHERE 1=1");
		sql.append(" AND da_date >= :begindate");
		sql.append(" AND da_date <= :enddate");
		sql.append(" AND da_shop_code IN(:shopCodes)");
		sql.append(" AND companyid = :companyid");
		sql.append(" GROUP BY DAY(da_date)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String day = rs.getString("day");
				if(!resultMap.containsKey(day)){
					resultMap.put(day, new KpiAnalysisDayDto());
				}
				resultMap.get(day).setComeamount(rs.getInt("comeAmount"));
				resultMap.get(day).setReceiveamount(rs.getInt("receiveAmount"));
				return null;
			};
		});
		//销售数量、销售金额、零售金额、成本金额
		sql.setLength(0);
		sql.append(" SELECT DAY(shl_date) AS day");
		sql.append(" ,IFNULL(SUM(shl_amount),0) AS sellAmount");
		sql.append(" ,IFNULL(SUM(shl_money),0) AS sellMoney");
		sql.append(" ,IFNULL(SUM(shl_sell_price*shl_amount),0) AS retailMoney");
		sql.append(" ,IFNULL(SUM(shl_cost_price*shl_amount),0) AS costMoney");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code IN(:shopCodes)");
		sql.append(" AND shl_date >= :begindate");
		sql.append(" AND shl_date <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY DAY(shl_date)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String day = rs.getString("day");
				if(!resultMap.containsKey(day)){
					resultMap.put(day, new KpiAnalysisDayDto());
				}
				resultMap.get(day).setSellamount(rs.getInt("sellAmount"));
				resultMap.get(day).setSellmoney(rs.getDouble("sellMoney"));
				resultMap.get(day).setRetailmoney(rs.getDouble("retailMoney"));
				resultMap.get(day).setCostmoney(rs.getDouble("costMoney"));
				resultMap.get(day).setProfits(rs.getDouble("sellMoney")-rs.getDouble("costMoney"));
				return null;
			};
		});
		//会员消费
		sql.setLength(0);
		sql.append(" SELECT DAY(shl_date) AS day,IFNULL(SUM(shl_money),0) AS sellMoney_vip");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code IN(:shopCodes)");
		sql.append(" AND shl_date >= :begindate");
		sql.append(" AND shl_date <= :enddate");
		sql.append(" AND shl_vip_code != ''");
		sql.append(" AND shl_vip_code IS NOT NULL");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY DAY(shl_date)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String day = rs.getString("day");
				if(!resultMap.containsKey(day)){
					resultMap.put(day, new KpiAnalysisDayDto());
				}
				resultMap.get(day).setSellmoney_vip(rs.getDouble("sellMoney_vip"));
				return null;
			};
		});
		//抹零金额
		sql.setLength(0);
		sql.append(" SELECT DAY(sh_date) AS day, SUM(sh_lost_money) AS lost_money");
		sql.append(" FROM t_sell_shop t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sh_shop_code IN(:shopCodes)");
		sql.append(" AND sh_date >= :begindate");
		sql.append(" AND sh_date <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY DAY(sh_date)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String day = rs.getString("day");
				if(!resultMap.containsKey(day)){
					resultMap.put(day, new KpiAnalysisDayDto());
				}
				resultMap.get(day).setLost_money(rs.getDouble("lost_money"));
				return null;
			};
		});
		//成交单数
		sql.setLength(0);
		sql.append(" SELECT DAY(shl_date) AS day,shl_state,COUNT(DISTINCT shl_number) AS dealCount");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code IN(:shopCodes)");
		sql.append(" AND shl_date >= :begindate");
		sql.append(" AND shl_date <= :enddate");
		sql.append(" AND shl_state IN(0,1)");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY DAY(shl_date),shl_state");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String day = rs.getString("day");
				if(!resultMap.containsKey(day)){
					resultMap.put(day, new KpiAnalysisDayDto());
				}
				if("0".equals(rs.getString("shl_state"))){
					resultMap.get(day).setDealcount(resultMap.get(day).getDealcount() + rs.getInt("dealCount"));
				}else {
					resultMap.get(day).setDealcount(resultMap.get(day).getDealcount() - rs.getInt("dealCount"));
				}
				return null;
			};
		});
		//开卡人数
		sql.setLength(0);
		sql.append(" SELECT DAY(vm_sysdate) AS day,COUNT(1) AS vipCount");
		sql.append(" FROM t_vip_member t");
		sql.append(" WHERE 1=1");
		sql.append(" AND vm_shop_code IN(:shopCodes)");
		sql.append(" AND vm_sysdate >= :begindate");
		sql.append(" AND vm_sysdate <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY DAY(vm_sysdate)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String day = rs.getString("day");
				if(!resultMap.containsKey(day)){
					resultMap.put(day, new KpiAnalysisDayDto());
				}
				resultMap.get(day).setVipcount(rs.getInt("vipCount"));
				return null;
			};
		});
		//新增储值卡
		sql.setLength(0);
		sql.append(" SELECT DAY(cd_grantdate) AS day,COUNT(1) AS cardCount");
		sql.append(" FROM t_sell_card t");
		sql.append(" WHERE 1=1");
		sql.append(" AND cd_shop_code IN(:shopCodes)");
		sql.append(" AND cd_grantdate >= :begindate");
		sql.append(" AND cd_grantdate <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY DAY(cd_grantdate)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String day = rs.getString("day");
				if(!resultMap.containsKey(day)){
					resultMap.put(day, new KpiAnalysisDayDto());
				}
				resultMap.get(day).setCardcount(rs.getInt("cardCount"));
				return null;
			};
		});
		
		//优惠券
		sql.setLength(0);
		sql.append(" SELECT DAY(ecu_date) day,COUNT(1) AS  ecouponCount");
		sql.append(" FROM t_sell_ecoupon_user t");
		sql.append(" WHERE 1=1");
		sql.append(" AND ecu_shop_code IN(:shopCodes)");
		sql.append(" AND ecu_date >= :begindate");
		sql.append(" AND ecu_date <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY DAY(ecu_date)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String day = rs.getString("day");
				if(!resultMap.containsKey(day)){
					resultMap.put(day, new KpiAnalysisDayDto());
				}
				resultMap.get(day).setEcouponcount(rs.getInt("ecouponCount"));
				return null;
			};
		});
		
		//成交率=成交单数/接待人数,连带率=销售数量/成交单数,会员消费占比,平均件单价,平均客单价
		for (String key : resultMap.keySet()) {
			KpiAnalysisDayDto dto = resultMap.get(key);
			if(dto.getDealcount() != 0){
				dto.setJoint_rate(dto.getSellamount() / (double) dto.getDealcount());
				dto.setAvg_sellprice(dto.getSellmoney() / (double) dto.getDealcount());
			}else {
				dto.setJoint_rate(0d);
				dto.setAvg_sellprice(0d);
			}
			if(dto.getReceiveamount() != 0){
				dto.setDeal_rate(100 * dto.getDealcount() / (double) dto.getReceiveamount());
			}else {
				dto.setDeal_rate(0d);
			}
			if(dto.getSellmoney() != 0){
				dto.setSellmoney_vip_proportion(100 * dto.getSellmoney_vip() / dto.getSellmoney());
				dto.setProfits_rate(100 * dto.getProfits() / dto.getSellmoney());
			}else {
				dto.setSellmoney_vip_proportion(0d);
				dto.setProfits_rate(0d);
			}
			if(dto.getSellamount() != 0){
				dto.setAvg_price(dto.getSellmoney()/dto.getSellamount());
			}else {
				dto.setAvg_price(0d);
			}
			if(dto.getPlanmoney() != 0){
				dto.setComplete_rate(100 * dto.getSellmoney() / dto.getPlanmoney());
			}else {
				dto.setComplete_rate(0d);
			}
		}
		return resultMap;
	}

	@Override
	public List<Map<String, Object>> brand_run(Map<String, Object> paramMap) {
		Object bd_code = paramMap.get("bd_code");
		Object pd_year = paramMap.get("pd_year");
		Object pd_season = paramMap.get("pd_season");
		Object tp_code = paramMap.get("tp_code");
		Object begindate = paramMap.get("begindate");
		Object enddate = paramMap.get("enddate");
		Object type = paramMap.get("type");
		String code = "";
		if("brand".equals(type)){
			code = "bd_code";
		}else if("type".equals(type)){
			code = "tp_code";
		}
		paramMap.put("begintime", begindate +" 00:00:00");
		paramMap.put("endtime", enddate +" 23:59:59");
		StringBuffer sql = new StringBuffer("");

		sql.append(" SELECT sd_id,SUM(sd_amount) sd_amount,pd_"+code+" bd_code,");
		if("brand".equals(type)){
			sql.append("bd_name");
		}else{
			sql.append("tp_name bd_name");
		}
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.sd_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		if("brand".equals(type)){
			sql.append(" JOIN t_base_brand b");
			sql.append(" ON b.bd_code=p.pd_bd_code");
			sql.append(" AND b.companyid=p.companyid");
		}else{
			sql.append(" JOIN t_base_type b");
			sql.append(" ON b.tp_code=p.pd_tp_code");
			sql.append(" AND b.companyid=p.companyid");
		}
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(pd_year)){
			sql.append(" AND pd_year=:pd_year");
		}
		if(StringUtil.isNotEmpty(pd_season)){
			sql.append(" AND pd_season=:pd_season");
		}
		sql.append(" AND t.sd_dp_code=:depot_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY pd_"+code);
		
		List<Map<String, Object>> maps = namedParameterJdbcTemplate.queryForList(sql.toString(), paramMap);
		
		if(null != maps && maps.size() > 0){
			sql.setLength(0);
			sql.append(" SELECT SUM(etl_amount) etl_amount,pd_"+code+" bd_code,etl_type");
			sql.append(" FROM t_buy_enterlist t");
			sql.append(" JOIN t_buy_enter e");
			sql.append(" ON e.et_number=t.etl_number");
			sql.append(" AND e.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.etl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND et_sysdate BETWEEN :begintime AND :endtime");
			}
			sql.append(" AND e.et_ar_state=1");
			sql.append(" AND e.et_depot_code=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_"+code+",etl_type");
			List<T_Buy_EnterList> buyList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Buy_EnterList.class));
			
			
			sql.setLength(0);
			sql.append(" SELECT SUM(sel_amount) sel_amount,pd_"+code+" bd_code,sel_type");
			sql.append(" FROM t_batch_selllist t");
			sql.append(" JOIN t_batch_sell e");
			sql.append(" ON e.se_number=t.sel_number");
			sql.append(" AND e.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.sel_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND se_sysdate BETWEEN :begintime AND :endtime");
			}
			sql.append(" AND e.se_ar_state=1");
			sql.append(" AND e.se_depot_code=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_"+code+",sel_type");
			List<T_Batch_SellList> batchList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Batch_SellList.class));
			sql.setLength(0);
			sql.append(" SELECT SUM(shl_amount) shl_amount,pd_"+code+" bd_code");
			sql.append(" FROM t_sell_shoplist t");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.shl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND shl_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND t.shl_dp_code=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_"+code);
			List<T_Sell_ShopList> shopList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
			
			sql.setLength(0);
			
			sql.append(" SELECT SUM(atl_sendamount) atl_sendamount,pd_"+code+" bd_code,atl_type");
			sql.append(" FROM t_sort_allotlist t");
			sql.append(" JOIN t_sort_allot a");
			sql.append(" ON a.at_number=t.atl_number");
			sql.append(" AND a.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.atl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND at_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND at_ar_state IN(3,4,5)");
			sql.append(" AND a.at_outdp_code=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_"+code+",atl_type");
			List<T_Sort_AllotList> sortList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Sort_AllotList.class));
			
			sql.setLength(0);
			sql.append(" SELECT SUM(wtl_sendamount),pd_"+code+" bd_code,wtl_type");
			sql.append(" FROM t_shop_wantlist t");
			sql.append(" JOIN t_shop_want a");
			sql.append(" ON a.wt_number=t.wtl_number");
			sql.append(" AND a.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.wtl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND wt_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND wt_ar_state IN(3,4,5)");
			sql.append(" AND a.wt_outdp_code=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_"+code+",wtl_type");
			List<T_Shop_WantList> wantList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Shop_WantList.class));
			
			sql.setLength(0);
			sql.append(" SELECT SUM(ajl_amount) ajl_amount,pd_"+code+" bd_code");
			sql.append(" FROM t_stock_adjustlist t");
			sql.append(" JOIN t_stock_adjust a");
			sql.append(" ON a.aj_number=t.ajl_number");
			sql.append(" AND a.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.ajl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND aj_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND aj_ar_state=1");
			sql.append(" AND a.aj_dp_code=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_"+code);
			List<T_Stock_AdjustList> adjustList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Stock_AdjustList.class));
			
			//仓库调出
			sql.setLength(0);
			sql.append(" SELECT SUM(acl_amount) acl_amount,pd_"+code+" bd_code");
			sql.append(" FROM t_stock_allocatelist t");
			sql.append(" JOIN t_stock_allocate a");
			sql.append(" ON a.ac_number=t.acl_number");
			sql.append(" AND a.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.acl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND ac_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND ac_ar_state=1");
			sql.append(" AND a.ac_outdp_code=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_"+code);
			List<T_Stock_AllocateList> stock_outList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Stock_AllocateList.class));
			
			sql.setLength(0);
			sql.append(" SELECT SUM(acl_amount) acl_amount,pd_"+code+" bd_code");
			sql.append(" FROM t_stock_allocatelist t");
			sql.append(" JOIN t_stock_allocate a");
			sql.append(" ON a.ac_number=t.acl_number");
			sql.append(" AND a.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.acl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND ac_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND ac_ar_state=1");
			sql.append(" AND a.ac_indp_code=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_"+code);
			List<T_Stock_AllocateList> stock_inList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Stock_AllocateList.class));
			
			
			//店铺调出
			sql.setLength(0);
			sql.append(" SELECT SUM(acl_amount) acl_amount,pd_"+code+" bd_code");
			sql.append(" FROM t_sell_allocatelist t");
			sql.append(" JOIN t_sell_allocate a");
			sql.append(" ON a.ac_number=t.acl_number");
			sql.append(" AND a.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.acl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND ac_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND ac_state IN(1,2,3)");
			sql.append(" AND a.ac_out_dp=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_"+code);
			List<T_Sell_AllocateList> sell_outList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Sell_AllocateList.class));
			
			sql.setLength(0);
			sql.append(" SELECT SUM(acl_amount) acl_amount,pd_"+code+" bd_code");
			sql.append(" FROM t_sell_allocatelist t");
			sql.append(" JOIN t_sell_allocate a");
			sql.append(" ON a.ac_number=t.acl_number");
			sql.append(" AND a.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.acl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND ac_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND ac_state=2");
			sql.append(" AND a.ac_in_dp=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_"+code);
			List<T_Sell_AllocateList> sell_inList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Sell_AllocateList.class));
			for(Map<String,Object> map:maps){
				if(null != buyList && buyList.size() > 0){
					for(T_Buy_EnterList item:buyList){
						if(item.getBd_code().equals(StringUtil.trimString(map.get("bd_code")))){
							if(item.getEtl_type() == 0){
								map.put("etl_amount", item.getEtl_amount());
							}
							if(item.getEtl_type() == 1){
								map.put("etb_amount", item.getEtl_amount());
							}
						}
					}
				}
				if(null != batchList && batchList.size() > 0){
					for(T_Batch_SellList item:batchList){
						if(item.getBd_code().equals(StringUtil.trimString(map.get("bd_code")))){
							if(item.getSel_type() == 0){
								map.put("sel_amount", item.getSel_amount());
							}
							if(item.getSel_type() == 1){
								map.put("seb_amount", item.getSel_amount());
							}
						}
					}
				}
				if(null != shopList && shopList.size() > 0){
					for(T_Sell_ShopList item:shopList){
						if(item.getBd_code().equals(StringUtil.trimString(map.get("bd_code")))){
							map.put("shl_amount", item.getShl_amount());
						}
					}
				}
				if(null != sortList && sortList.size() > 0){
					for(T_Sort_AllotList item:sortList){
						if(item.getBd_code().equals(StringUtil.trimString(map.get("bd_code")))){
							if(item.getAtl_type() == 0){
								map.put("atl_amount", item.getAtl_sendamount());
							}
							if(item.getAtl_type() == 1){
								map.put("atb_amount", item.getAtl_sendamount());
							}
						}
					}
				}
				
				if(null != wantList && wantList.size() > 0){
					for(T_Shop_WantList item:wantList){
						if(item.getBd_code().equals(StringUtil.trimString(map.get("bd_code")))){
							if(item.getWtl_type() == 0){
								map.put("wtl_amount", item.getWtl_sendamount());
							}
							if(item.getWtl_type() == 1){
								map.put("wtb_amount", item.getWtl_sendamount());
							}
						}
					}
				}
				if(null != adjustList && adjustList.size() > 0){
					for(T_Stock_AdjustList item:adjustList){
						if(item.getBd_code().equals(StringUtil.trimString(map.get("bd_code")))){
							map.put("ajl_amount", item.getAjl_amount());
						}
					}
				}
				if(null != stock_outList && stock_outList.size() > 0){
					for(T_Stock_AllocateList item:stock_outList){
						if(item.getBd_code().equals(StringUtil.trimString(map.get("bd_code")))){
							map.put("acl_dp_out", item.getAcl_amount());
						}
					}
				}
				if(null != stock_inList && stock_inList.size() > 0){
					for(T_Stock_AllocateList item:stock_inList){
						if(item.getBd_code().equals(StringUtil.trimString(map.get("bd_code")))){
							map.put("acl_dp_in", item.getAcl_amount());
						}
					}
				}
				if(null != sell_outList && sell_outList.size() > 0){
					for(T_Sell_AllocateList item:sell_outList){
						if(item.getBd_code().equals(StringUtil.trimString(map.get("bd_code")))){
							map.put("acl_sp_out", item.getAcl_amount());
						}
					}
				}
				if(null != sell_inList && sell_inList.size() > 0){
					for(T_Sell_AllocateList item:sell_inList){
						if(item.getBd_code().equals(StringUtil.trimString(map.get("bd_code")))){
							map.put("acl_sp_in", item.getAcl_amount());
						}
					}
				}
			}
		}
		return maps;
	}

	@Override
	public Integer product_run_count(Map<String, Object> paramMap) {
		Object bd_code = paramMap.get("bd_code");
		Object pd_year = paramMap.get("pd_year");
		Object pd_season = paramMap.get("pd_season");
		Object tp_code = paramMap.get("tp_code");
		StringBuffer sql = new StringBuffer("");

		sql.append(" SELECT count(1)");
		sql.append(" FROM (SELECT 1");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.sd_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(pd_year)){
			sql.append(" AND pd_year=:pd_year");
		}
		if(StringUtil.isNotEmpty(pd_season)){
			sql.append(" AND pd_season=:pd_season");
		}
		sql.append(" AND t.sd_dp_code=:depot_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY sd_pd_code) t");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), paramMap ,Integer.class);
	}
	private List<String> buildList(List<Map<String,Object>> list){
		List<String> strList = new ArrayList<String>();
		for(Map<String,Object> map:list){
			strList.add(StringUtil.trimString(map.get("pd_code")));
		}
		return strList;
	}
	@Override
	public List<Map<String, Object>> product_run_list(Map<String, Object> paramMap) {
		Object bd_code = paramMap.get("bd_code");
		Object pd_year = paramMap.get("pd_year");
		Object pd_season = paramMap.get("pd_season");
		Object tp_code = paramMap.get("tp_code");
		Object begindate = paramMap.get("begindate");
		Object enddate = paramMap.get("enddate");
		paramMap.put("begintime", begindate +" 00:00:00");
		paramMap.put("endtime", enddate +" 23:59:59");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT SUM(sd_amount) sd_amount,pd_code,pd_name");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.sd_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(pd_year)){
			sql.append(" AND pd_year=:pd_year");
		}
		if(StringUtil.isNotEmpty(pd_season)){
			sql.append(" AND pd_season=:pd_season");
		}
		sql.append(" AND t.sd_dp_code=:depot_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY sd_pd_code");
		sql.append(" ORDER BY sd_amount");
		sql.append(" LIMIT :start,:end");
		List<Map<String,Object>> list = namedParameterJdbcTemplate.queryForList(sql.toString(), paramMap);
		if(null != list && list.size() > 0){
			paramMap.put("pd_codes", buildList(list));
			sql.setLength(0);
			sql.append(" SELECT SUM(etl_amount) etl_amount,etl_pd_code,etl_type");
			sql.append(" FROM t_buy_enterlist t");
			sql.append(" JOIN t_buy_enter e");
			sql.append(" ON e.et_number=t.etl_number");
			sql.append(" AND e.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.etl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND et_sysdate BETWEEN :begintime AND :endtime");
			}
			sql.append(" AND e.et_ar_state=1");
			sql.append(" AND pd_code IN (:pd_codes)");
			sql.append(" AND e.et_depot_code=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_code,etl_type");
			List<T_Buy_EnterList> buyList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Buy_EnterList.class));
			
			
			sql.setLength(0);
			sql.append(" SELECT SUM(sel_amount) sel_amount,sel_pd_code,sel_type");
			sql.append(" FROM t_batch_selllist t");
			sql.append(" JOIN t_batch_sell e");
			sql.append(" ON e.se_number=t.sel_number");
			sql.append(" AND e.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.sel_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND se_sysdate BETWEEN :begintime AND :endtime");
			}
			sql.append(" AND e.se_ar_state=1");
			sql.append(" AND pd_code IN (:pd_codes)");
			sql.append(" AND e.se_depot_code=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_code,sel_type");
			List<T_Batch_SellList> batchList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Batch_SellList.class));
			sql.setLength(0);
			sql.append(" SELECT SUM(shl_amount) shl_amount,shl_pd_code");
			sql.append(" FROM t_sell_shoplist t");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.shl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND shl_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND pd_code IN (:pd_codes)");
			sql.append(" AND t.shl_dp_code=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_code");
			List<T_Sell_ShopList> shopList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
			
			sql.setLength(0);
			
			sql.append(" SELECT SUM(atl_sendamount) atl_sendamount,atl_pd_code,atl_type");
			sql.append(" FROM t_sort_allotlist t");
			sql.append(" JOIN t_sort_allot a");
			sql.append(" ON a.at_number=t.atl_number");
			sql.append(" AND a.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.atl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND at_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND at_ar_state IN(3,4,5)");
			sql.append(" AND pd_code IN (:pd_codes)");
			sql.append(" AND a.at_outdp_code=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_code,atl_type");
			List<T_Sort_AllotList> sortList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Sort_AllotList.class));
			
			sql.setLength(0);
			sql.append(" SELECT SUM(wtl_sendamount),wtl_pd_code,wtl_type");
			sql.append(" FROM t_shop_wantlist t");
			sql.append(" JOIN t_shop_want a");
			sql.append(" ON a.wt_number=t.wtl_number");
			sql.append(" AND a.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.wtl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND wt_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND wt_ar_state IN(3,4,5)");
			sql.append(" AND pd_code IN (:pd_codes)");
			sql.append(" AND a.wt_outdp_code=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_code,wtl_type");
			List<T_Shop_WantList> wantList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Shop_WantList.class));
			
			sql.setLength(0);
			sql.append(" SELECT SUM(ajl_amount) ajl_amount,ajl_pd_code");
			sql.append(" FROM t_stock_adjustlist t");
			sql.append(" JOIN t_stock_adjust a");
			sql.append(" ON a.aj_number=t.ajl_number");
			sql.append(" AND a.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.ajl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND aj_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND aj_ar_state=1");
			sql.append(" AND pd_code IN (:pd_codes)");
			sql.append(" AND a.aj_dp_code=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_code");
			List<T_Stock_AdjustList> adjustList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Stock_AdjustList.class));
			
			//仓库调出
			sql.setLength(0);
			sql.append(" SELECT SUM(acl_amount) acl_amount,acl_pd_code");
			sql.append(" FROM t_stock_allocatelist t");
			sql.append(" JOIN t_stock_allocate a");
			sql.append(" ON a.ac_number=t.acl_number");
			sql.append(" AND a.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.acl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND ac_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND ac_ar_state=1");
			sql.append(" AND pd_code IN (:pd_codes)");
			sql.append(" AND a.ac_outdp_code=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_code");
			List<T_Stock_AllocateList> stock_outList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Stock_AllocateList.class));
			
			sql.setLength(0);
			sql.append(" SELECT SUM(acl_amount) acl_amount,acl_pd_code");
			sql.append(" FROM t_stock_allocatelist t");
			sql.append(" JOIN t_stock_allocate a");
			sql.append(" ON a.ac_number=t.acl_number");
			sql.append(" AND a.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.acl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND ac_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND ac_ar_state=1");
			sql.append(" AND pd_code IN (:pd_codes)");
			sql.append(" AND a.ac_indp_code=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_code");
			List<T_Stock_AllocateList> stock_inList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Stock_AllocateList.class));
			
			
			//店铺调出
			sql.setLength(0);
			sql.append(" SELECT SUM(acl_amount) acl_amount,acl_pd_code");
			sql.append(" FROM t_sell_allocatelist t");
			sql.append(" JOIN t_sell_allocate a");
			sql.append(" ON a.ac_number=t.acl_number");
			sql.append(" AND a.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.acl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND ac_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND ac_state IN(1,2,3)");
			sql.append(" AND pd_code IN (:pd_codes)");
			sql.append(" AND a.ac_out_dp=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_code");
			List<T_Sell_AllocateList> sell_outList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Sell_AllocateList.class));
			
			sql.setLength(0);
			sql.append(" SELECT SUM(acl_amount) acl_amount,acl_pd_code");
			sql.append(" FROM t_sell_allocatelist t");
			sql.append(" JOIN t_sell_allocate a");
			sql.append(" ON a.ac_number=t.acl_number");
			sql.append(" AND a.companyid=t.companyid");
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.acl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(pd_year)){
				sql.append(" AND pd_year=:pd_year");
			}
			if(StringUtil.isNotEmpty(pd_season)){
				sql.append(" AND pd_season=:pd_season");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND ac_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND ac_state=2");
			sql.append(" AND pd_code IN (:pd_codes)");
			sql.append(" AND a.ac_in_dp=:depot_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY pd_code");
			List<T_Sell_AllocateList> sell_inList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Sell_AllocateList.class));
			for(Map<String,Object> map:list){
				if(null != buyList && buyList.size() > 0){
					for(T_Buy_EnterList item:buyList){
						if(item.getEtl_pd_code().equals(StringUtil.trimString(map.get("pd_code")))){
							if(item.getEtl_type() == 0){
								map.put("etl_amount", item.getEtl_amount());
							}
							if(item.getEtl_type() == 1){
								map.put("etb_amount", item.getEtl_amount());
							}
						}
					}
				}
				if(null != batchList && batchList.size() > 0){
					for(T_Batch_SellList item:batchList){
						if(item.getSel_pd_code().equals(StringUtil.trimString(map.get("pd_code")))){
							if(item.getSel_type() == 0){
								map.put("sel_amount", item.getSel_amount());
							}
							if(item.getSel_type() == 1){
								map.put("seb_amount", item.getSel_amount());
							}
						}
					}
				}
				if(null != shopList && shopList.size() > 0){
					for(T_Sell_ShopList item:shopList){
						if(item.getShl_pd_code().equals(StringUtil.trimString(map.get("pd_code")))){
							map.put("shl_amount", item.getShl_amount());
						}
					}
				}
				if(null != sortList && sortList.size() > 0){
					for(T_Sort_AllotList item:sortList){
						if(item.getAtl_pd_code().equals(StringUtil.trimString(map.get("pd_code")))){
							if(item.getAtl_type() == 0){
								map.put("atl_amount", item.getAtl_sendamount());
							}
							if(item.getAtl_type() == 1){
								map.put("atb_amount", item.getAtl_sendamount());
							}
						}
					}
				}
				
				if(null != wantList && wantList.size() > 0){
					for(T_Shop_WantList item:wantList){
						if(item.getWtl_pd_code().equals(StringUtil.trimString(map.get("pd_code")))){
							if(item.getWtl_type() == 0){
								map.put("wtl_amount", item.getWtl_sendamount());
							}
							if(item.getWtl_type() == 1){
								map.put("wtb_amount", item.getWtl_sendamount());
							}
						}
					}
				}
				if(null != adjustList && adjustList.size() > 0){
					for(T_Stock_AdjustList item:adjustList){
						if(item.getAjl_pd_code().equals(StringUtil.trimString(map.get("pd_code")))){
							map.put("ajl_amount", item.getAjl_amount());
						}
					}
				}
				if(null != stock_outList && stock_outList.size() > 0){
					for(T_Stock_AllocateList item:stock_outList){
						if(item.getAcl_pd_code().equals(StringUtil.trimString(map.get("pd_code")))){
							map.put("acl_dp_out", item.getAcl_amount());
						}
					}
				}
				if(null != stock_inList && stock_inList.size() > 0){
					for(T_Stock_AllocateList item:stock_inList){
						if(item.getAcl_pd_code().equals(StringUtil.trimString(map.get("pd_code")))){
							map.put("acl_dp_in", item.getAcl_amount());
						}
					}
				}
				if(null != sell_outList && sell_outList.size() > 0){
					for(T_Sell_AllocateList item:sell_outList){
						if(item.getAcl_pd_code().equals(StringUtil.trimString(map.get("pd_code")))){
							map.put("acl_sp_out", item.getAcl_amount());
						}
					}
				}
				if(null != sell_inList && sell_inList.size() > 0){
					for(T_Sell_AllocateList item:sell_inList){
						if(item.getAcl_pd_code().equals(StringUtil.trimString(map.get("pd_code")))){
							map.put("acl_sp_in", item.getAcl_amount());
						}
					}
				}
			}
		}
		return list;
	}
	
}
